from bs4 import BeautifulSoup
from bs4 import UnicodeDammit
import urllib.request
import sqlite3

# 创建 SQLite 数据库连接
conn = sqlite3.connect('weather_data.db')
cursor = conn.cursor()

# 创建一个表用于存储天气数据
cursor.execute('''
    CREATE TABLE IF NOT EXISTS WeatherData (
        id INTEGER PRIMARY KEY,
        area TEXT,
        date TEXT,
        weather TEXT,
        temperature TEXT
    )
''')

url = "http://www.weather.com.cn/weather/101010100.shtml"
try:
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36 Edg/117.0.2045.47"}
    req = urllib.request.Request(url, headers=headers)
    data = urllib.request.urlopen(req)
    data = data.read()
    dammit = UnicodeDammit(data, ["utf-8", "gbk"])
    data = dammit.unicode_markup
    soup = BeautifulSoup(data, "lxml")
    lis = soup.select("ul[class='t clearfix'] li")
    i = 0
    print("{:<10}\t{:<10}\t{:<10}\t\t{:<10}{:<10}".format("id","地区","日期","天气信息","温度"))
    for li in lis:
        try:
            i = i + 1
            date = li.select('h1')[0].text
            weather = li.select('p[class="wea"]')[0].text
            temp = li.select('p[class="tem"] span')[0].text + "/" + li.select('p[class="tem"] i')[0].text
            # 将数据插入到数据库中
            cursor.execute('''
                INSERT INTO WeatherData (area, date, weather, temperature) VALUES (?, ?, ?, ?)
            ''', ("北京", date, weather, temp))
            conn.commit()  # 提交事务

            print("{:<10}\t{:<10}\t{:<10}\t{:<10}\t{:<10}".format(i, "北京", date, weather, temp))
        except Exception as err:
            print(err)
except Exception as err:
    print(err)
finally:
    # 关闭数据库连接
    conn.close()


conn = sqlite3.connect('weather_data.db')
c = conn.cursor()


for row in c.execute('SELECT * FROM WeatherData'):
    print(row)


conn.close()